package repositories;

import java.util.Collection;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import domain.Customer;

@Repository
public interface CustomerRepository extends JpaRepository<Customer,Integer>{
	
	// Find a specific customer by username
	@Query("select c from Customer c where c.userAccount.username = ?1")
	Customer findCustomerByUsername(String username);
	
	// Find the customer with more paintings
	@Query("select oAux.legalOwner from Ownership oAux where oAux.finishDate = null group by oAux.legalOwner having count(oAux)>= ALL(select count(oAux2) from Ownership oAux2 where oAux2.finishDate = null group by oAux2.legalOwner)")
	Collection<Customer> findCustomerWithMorePaintings();
	
	// Find the customer with more comments
	@Query("select c from Customer c where c.comments.size = (select max(cAux.comments.size) from Customer cAux)")
	Collection<Customer> findCustomerWithMoreComments();
	
}
